package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Creates all of the tables in the database.
 * 
 * @author Brad Hanlon
 *
 */
public class CreateTables {
	
	/**
	 * Creates all of the tables in the database.
	 * 
	 * @param conn
	 */
	public static void createAllTables(Connection conn){
		createBudgetTable(conn);
		createCategoryTable(conn);
		createSubCategoryTable(conn);
		createTransactionsTable(conn);
		createAssociatesTable(conn);
		createMemberTable(conn);
		createNonMemberTable(conn);
		createVendorTable(conn);
		createAccountTable(conn);
		createTransHasAssociateTable(conn);
		createAdjustsTable(conn);
		createBudgetHasTrans(conn);
		createBudgetHasCategory(conn);
		createCategoryHasSubCategory(conn);
	}
	
	/**
	 * Drops all of the tables in the database.
	 * 
	 * @param conn
	 */
	public static void dropTables(Connection conn){
		try{
			PreparedStatement ps_drop;
			ps_drop = conn.prepareStatement("DROP TABLE CategoryHasSubCategory");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE BudgetHasCategory");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE BudgetHasTrans");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE Adjusts");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE TransHasAssociate");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE Account");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE Vendor");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE NonMember");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE Member");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE Associate");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE Transactions");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE SubCategory");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE Category");
			ps_drop.executeUpdate();
			ps_drop = conn.prepareStatement("DROP TABLE Budget");
			ps_drop.executeUpdate();
		} 
		catch (SQLException e) {
			System.out.println("Cannot drop table - table does not exist");
		}
	}
	
	/**
	 * Create Budget table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createBudgetTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE Budget (" + 
						"title VARCHAR(32) NOT NULL," +
						"currentBalance INTEGER," +
						"totalIncome INTEGER," +
						"totalExpenses INTEGER," +
						"PRIMARY KEY (title))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Create Category table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createCategoryTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE Category (" + 
						"name VARCHAR(32) NOT NULL," +
						"PRIMARY KEY (name))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			// Income
			String insert_1 = "INSERT INTO Category (name) VALUES ('A. Dues')";
			String insert_2 = "INSERT INTO Category (name) VALUES ('B. Contributions')";
			String insert_3 = "INSERT INTO Category (name) VALUES ('C. Other Inc Interest Inc')";
			String insert_4 = "INSERT INTO Category (name) VALUES ('D. Miscellaneous')";
			
			// Expenses
			String insert_5 = "INSERT INTO Category (name) VALUES ('A. Operating Costs')";
			String insert_6 = "INSERT INTO Category (name) VALUES ('B. Board and Committees')";
			String insert_7 = "INSERT INTO Category (name) VALUES ('C. Delegates, Travel & Workshop')";
			String insert_8 = "INSERT INTO Category (name) VALUES ('D. Financial Support for League')";
			String insert_9 = "INSERT INTO Category (name) VALUES ('E. Newsletter')";
			String insert_10 = "INSERT INTO Category (name) VALUES ('F. Educational Activity')";
			String insert_11 = "INSERT INTO Category (name) VALUES ('G. Position Support')";
			String insert_12 = "INSERT INTO Category (name) VALUES ('H. Misc')";
			
			ps = conn.prepareStatement(insert_1);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_2);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_3);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_4);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_5);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_6);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_7);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_8);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_9);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_10);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_11);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_12);
			ps.executeUpdate();
			 
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Create SubCategory table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createSubCategoryTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE SubCategory (" + 
						"name VARCHAR(32) NOT NULL," +
						"lobbyingExpense INTEGER," +
						"projectedBudget INTEGER," +
						"PRIMARY KEY (name))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
			
			// Contributions
			String insert_1 = "INSERT INTO SubCategory (name) VALUES ('1. Member Contributions')";
			String insert_2 = "INSERT INTO SubCategory (name) VALUES ('2. Member Ed Fund')";
			String insert_3 = "INSERT INTO SubCategory (name) VALUES ('3. Non-member Contribution')";
			String insert_4 = "INSERT INTO SubCategory (name) VALUES ('4. Non-member Ed Fund')";
			String insert_5 = "INSERT INTO SubCategory (name) VALUES ('5. In Kind Donation')";
			
			// Other Inc Interest Inc
			String insert_6 = "INSERT INTO SubCategory (name) VALUES ('1. Womens Equality Day')";
			
			// Miscellaneous
			String insert_7 = "INSERT INTO SubCategory (name) VALUES ('1. Holiday Brunch')";
			String insert_8 = "INSERT INTO SubCategory (name) VALUES ('2. Annual Meeting')";
			String insert_9 = "INSERT INTO SubCategory (name) VALUES ('3. Transfer from LWV Ed Fund')";
			String insert_10 = "INSERT INTO SubCategory (name) VALUES ('4. Transfer from Savings or CD')";
			
			// Operating Costs
			String insert_11 = "INSERT INTO SubCategory (name) VALUES ('1. General Supplies')";
			String insert_12 = "INSERT INTO SubCategory (name) VALUES ('2. Telephone and Answering Svc')";
			String insert_13 = "INSERT INTO SubCategory (name) VALUES ('3. Post Office Box')";
			String insert_14 = "INSERT INTO SubCategory (name) VALUES ('4. Insurance')";
			String insert_35 = "INSERT INTO SubCategory (name) VALUES ('5. Scholarship')";
			String insert_36 = "INSERT INTO SubCategory (name) VALUES ('6. PayPal Fees')";
			String insert_37 = "INSERT INTO SubCategory (name) VALUES ('7. Corporate Filing Fee')";
			
			// Board and Committees
			String insert_15 = "INSERT INTO SubCategory (name) VALUES ('1. Finance Committee')";
			String insert_16 = "INSERT INTO SubCategory (name) VALUES ('2. Membership Expenses')";
			String insert_17 = "INSERT INTO SubCategory (name) VALUES ('3. President Expenses')";
			
			// Delegates, Travel & Workshop
			String insert_18 = "INSERT INTO SubCategory (name) VALUES ('1. State Annual Meeting')";
			String insert_19 = "INSERT INTO SubCategory (name) VALUES ('2. National Convention')";
			String insert_20 = "INSERT INTO SubCategory (name) VALUES ('3. Workshops')";
			
			// Financial Support for League
			String insert_21 = "INSERT INTO SubCategory (name) VALUES ('1. Lake Michigan Interleague')";
			String insert_22 = "INSERT INTO SubCategory (name) VALUES ('2. State PMA')";
			String insert_23 = "INSERT INTO SubCategory (name) VALUES ('3. National PMP')";
			
			// Educational Activity
			String insert_24 = "INSERT INTO SubCategory (name) VALUES ('1. Program - National')";
			String insert_25 = "INSERT INTO SubCategory (name) VALUES ('2. Program - State')";
			String insert_26 = "INSERT INTO SubCategory (name) VALUES ('3. Program - Local')";
			String insert_27 = "INSERT INTO SubCategory (name) VALUES ('4. Voters Service')";
			String insert_28 = "INSERT INTO SubCategory (name) VALUES ('5. Website')";
			String insert_29 = "INSERT INTO SubCategory (name) VALUES ('6. Archive')";
			
			// Misc
			String insert_30 = "INSERT INTO SubCategory (name) VALUES ('1. Corporate Filing Fee')";
			String insert_31 = "INSERT INTO SubCategory (name) VALUES ('2. Holiday Brunch')";
			String insert_32 = "INSERT INTO SubCategory (name) VALUES ('3. Annual Meeting')";
			String insert_33 = "INSERT INTO SubCategory (name) VALUES ('4. In Kind Expense')";
			String insert_34 = "INSERT INTO SubCategory (name) VALUES ('5. Womens Equality Day')";
			
			ps = conn.prepareStatement(insert_1);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_2);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_3);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_4);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_5);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_6);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_7);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_8);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_9);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_10);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_11);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_12);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_13);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_14);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_35);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_36);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_37);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_15);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_16);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_17);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_18);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_19);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_20);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_21);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_22);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_23);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_24);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_25);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_26);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_27);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_28);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_29);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_30);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_31);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_32);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_33);
			ps.executeUpdate();
			ps = conn.prepareStatement(insert_34);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Create Transactions table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createTransactionsTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE Transactions (" + 
						"transID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
						"transDate DATE NOT NULL," + 
						"category VARCHAR(32)," +
						"subCategory VARCHAR(32)," +
						"paymentAmount DECIMAL(10,2)," +
						"checkNumber INTEGER," +
						"action VARCHAR(32)," +
						"associateID VARCHAR(32)," +
						"note VARCHAR(255)," +
						"depositAmount DECIMAL(10,2)," +
						"PRIMARY KEY (transID))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Creates the Associates table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createAssociatesTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE Associate (" + 
						"idNum INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
						"address VARCHAR(50)," +
						"city VARCHAR(32)," +
						"state VARCHAR(2)," +
						"zip INTEGER," +
						"zip4 INTEGER," +
						"phoneNumber VARCHAR(10)," +
						"email VARCHAR(50)," +
						"PRIMARY KEY (idNum))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Creates the Member table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createMemberTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE Member (" + 
						"idNum INTEGER NOT NULL," +
						"firstName VARCHAR(32)," +
						"lastName VARCHAR(32)," +
						"PRIMARY KEY (idNum)," +
						"FOREIGN KEY (idNum) REFERENCES Associate (idNum))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Creates the NonMember table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createNonMemberTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE NonMember (" + 
						"idNum INTEGER NOT NULL," +
						"fullName VARCHAR(32)," +
						"PRIMARY KEY (idNum)," +
						"FOREIGN KEY (idNum) REFERENCES Associate (idNum))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Creates the Vendor table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createVendorTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE Vendor (" + 
						"idNum INTEGER NOT NULL," +
						"vendorName VARCHAR(32)," +
						"PRIMARY KEY (idNum)," +
						"FOREIGN KEY (idNum) REFERENCES Associate (idNum))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Creates the Account table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createAccountTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE Account (" + 
						"accountNum INTEGER NOT NULL," +
						"balance INTEGER," +
						"name VARCHAR(32)," +
						"PRIMARY KEY (accountNum))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Creates the TransHasAssociate table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createTransHasAssociateTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE TransHasAssociate (" + 
						"transID INTEGER NOT NULL," +
						"idNum INTEGER NOT NULL," +
						"PRIMARY KEY (transID, idNum)," +
						"FOREIGN KEY (transID) REFERENCES Transactions (transID)," +
						"FOREIGN KEY (idNum) REFERENCES Associate (idNum))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Creates the Adjusts table.
	 * 
	 * @param conn - java.sql.Connection - Get from DatabaseConnector
	 */
	public static void createAdjustsTable(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE Adjusts (" + 
						"accountNum INTEGER NOT NULL," +
						"transID INTEGER NOT NULL," +
						"PRIMARY KEY (accountNum, transID)," +
						"FOREIGN KEY (transID) REFERENCES Transactions (transID)," +
						"FOREIGN KEY (accountNum) REFERENCES Account (accountNum))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void createBudgetHasTrans(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE BudgetHasTrans (" + 
						"title VARCHAR(32) NOT NULL," +
						"transID INTEGER NOT NULL," +
						"PRIMARY KEY (title, transID)," +
						"FOREIGN KEY (title) REFERENCES Budget (title)," +
						"FOREIGN KEY (transID) REFERENCES Transactions (transID))";
		try {
			ps = conn.prepareStatement(query); 
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void createBudgetHasCategory(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE BudgetHasCategory (" + 
						"title VARCHAR(32) NOT NULL," +
						"name VARCHAR(32) NOT NULL," +
						"PRIMARY KEY (title, name)," +
						"FOREIGN KEY (title) REFERENCES Budget (title)," +
						"FOREIGN KEY (name) REFERENCES Category (name))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void createCategoryHasSubCategory(Connection conn){
		PreparedStatement ps;
		String query = 
				"CREATE TABLE CategoryHasSubCategory (" + 
						"catName VARCHAR(32) NOT NULL," +
						"subName VARCHAR(32) NOT NULL," +
						"PRIMARY KEY (catName, subName)," +
						"FOREIGN KEY (catName) REFERENCES Category (name)," +
						"FOREIGN KEY (subName) REFERENCES SubCategory (name))";
		try {
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
